﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace TMS
{
    class Agreements
    {
        private int ID;
        private string Model;
        private string Registration;
        private string Driver;

        public Agreements()
        {

        }

        //sluzi za brisenje
        public Agreements(int ID)
        {
            this.ID = ID;
        }

        //za proverka na postoecko vozilo vo baza
        public Agreements(string Registration)
        {
            this.Registration = Registration;
        }

        public Agreements(int ID, string Model, string Registration, string Driver)
        {
            this.ID = ID;
            this.Model = Model;
            this.Registration = Registration;
            this.Driver = Driver;
        }

        public DataSet GetAgreementFromDBAsDataSet(int agreementID)
        {

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM vehicles WHERE ID=''";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result;

        }

        public void InsertAgreementInDB()
        {

            //string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            //MySqlConnection mysqlCon = new MySqlConnection(connString);

            //mysqlCon.Open();


            //string sqlInsertVehicle = "INSERT INTO vehicles VALUES (DEFAULT,'" +
            //                            this.Model + "','" +
            //                            this.Registration + "','" +
            //                            this.Driver + "')";

            //MySqlCommand cmd = new MySqlCommand(sqlInsertVehicle, mysqlCon);
            //cmd.ExecuteNonQuery();

            //mysqlCon.Close();
        }

        public void UpdateAgreementInDB()
        {

            //string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            //MySqlConnection mysqlCon = new MySqlConnection(connString);

            //mysqlCon.Open();

            //string sqlUpdateVehicle = "UPDATE vehicles " +
            //                          "SET Model = '" + this.Model + "'," +
            //                          "Registration = '" + this.Registration + "', " +
            //                          "Driver = '" + this.Driver + "' " +
            //                          "WHERE ID = " + this.ID;


            //MySqlCommand cmd = new MySqlCommand(sqlUpdateVehicle, mysqlCon);
            //cmd.ExecuteNonQuery();

            //mysqlCon.Close();

        }

        public void DeleteAgreementFromDB()
        {

            //string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            //MySqlConnection mysqlCon = new MySqlConnection(connString);

            //mysqlCon.Open();

            //string sqlDeleteVehicle = "DELETE FROM vehicles " +
            //                          "WHERE ID = " + this.ID;

            //MySqlCommand cmd = new MySqlCommand(sqlDeleteVehicle, mysqlCon);
            //cmd.ExecuteNonQuery();

            //mysqlCon.Close();
        }

        public bool CheckExistingAgreementInDB()
        {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlCheckExistingVehicle = "SELECT * FROM vehicles " +
                                             "WHERE Registration = '" + this.Registration + "'";

            MySqlCommand cmd = new MySqlCommand(sqlCheckExistingVehicle, mysqlCon);

            object result = cmd.ExecuteScalar();
            mysqlCon.Close();

            if (result != DBNull.Value && result != null)
            {
                //record exists
                return true;
            }
            else
            {
                //does not exist
                return false;
            }
        }

        public DataSet GetAllAgreementsFromDB()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            DataSet table = new DataSet();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM vehicles";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            MyDA.Fill(table);

            mysqlCon.Close();

            return table;
        }

    }
}
